﻿using Dapper;
using Furion.DatabaseAccessor;
using Furion.DynamicApiController;
using FurStudy.Core;
using FurStudy.EntityFramework.Core;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using MongoDB.Bson;
using MongoDB.Driver;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static FurStudy.Core.IShape;

namespace FurStudy.Application
{
    public class PersonService : IDynamicApiController
    {
        #region 构造方法注入

        /// <summary>
        /// 可调配仓储（读写分离）
        /// </summary>
        private readonly IMSRepository<MasterDbContextLocator, SlaveDbContextLocator> _msRepository;
        // private readonly IMSRepository _msRepository;   // 不指定定位器，默认是 MasterDbContextLocator

        private readonly ISqlSugarRepository repository; // 仓储对象：封装简单的CRUD
        private readonly SqlSugarClient db; // 核心对象：拥有完整的SqlSugar全部功能

        private readonly IDapperRepository _dapperRepository; // 非泛型版本
        private readonly IDapperRepository<Person> _dapperTRepository; // 泛型版本

        private readonly IMongoDBRepository _mongoDBRepository; // 非泛型版本

        /// <summary>
        /// 构造函数初始化
        /// </summary>
        /// <param name="msRepository"></param>
        public PersonService(IMSRepository<MasterDbContextLocator, SlaveDbContextLocator> msRepository,
            ISqlSugarRepository sqlSugarRepository,
            IDapperRepository dapperRepository,
            IDapperRepository<Person> dapperTRepository,
            IMongoDBRepository mongoDBRepository)
        {
            _msRepository = msRepository;
            repository = sqlSugarRepository;
            db = repository.Context; // 推荐操作
            _dapperRepository = dapperRepository; // 非泛型版本
            _dapperTRepository = dapperTRepository; // 泛型版本
            _mongoDBRepository = mongoDBRepository; // 非泛型版本
        }

        #endregion

        #region 属性方式注入

        public IRepository Repository { get; set; }

        #endregion

        #region 方法参数注入

        //public Person GetById([FromServices] IRepository repository, int id)
        //{
        //    return repository.Find(id);
        //}

        #endregion


        ///// <summary>
        ///// 构造函数初始化(不指定定位器，默认是 MasterDbContextLocator)
        ///// </summary>
        ///// <param name="msRepository"></param>
        //public PersonService(IMSRepository msRepository)
        //{
        //    _msRepository = msRepository;
        //}

        /// <summary>
        /// 新增走主库（写主库）
        /// </summary>
        /// <param name="person"></param>
        public void Insert(Person_MasterSlaveLibrary person)
        {
            _msRepository.Master<Person_MasterSlaveLibrary>().Insert(person);
        }

        /// <summary>
        /// 查询走从库（读从库） - 随机
        /// </summary>
        /// <returns></returns>
        public List<Person_MasterSlaveLibrary> Get()
        {
            return _msRepository.Slave1<Person_MasterSlaveLibrary>().AsEnumerable().ToList();
        }

        /// <summary>
        /// 自定义从库
        /// </summary>
        /// <returns></returns>
        //public List<Person_MasterSlaveLibrary> GetLibrary()
        //{
        //    return _msRepository.Slave1<Person_MasterSlaveLibrary>(() => {
        //        // 这里写你的逻辑返回从库定位器
        //        return 你的从库定位器;
        //    }).AsEnumerable().ToList();
        //}

        #region EFCore高性能

        //// Create an explicitly compiled query
        //private static Func<CustomerContext, int, Customer> _customerById =
        //    EF.CompileQuery((CustomerContext db, int id) =>
        //        db.Customers
        //            .Include(c => c.Address)
        //            .Single(c => c.Id == id));

        //// Use the compiled query by invoking it
        //using (var db = new CustomerContext())
        //{
        //   var customer = _customerById(db, 147);
        //}

        #endregion

        #region SqlSugar

        public void SqlSugarDemo()
        {
            // 查询功能
            var data1 = db.Queryable<Student>().First(it => it.Id == 1); // db.GetById(1);
            var data2 = db.Queryable<Student>().ToList(); // db.GetList();

            #region 简单仓储

            // ================== 简单仓储 ================

            // 插入
            //repository.Insert(insertObj);
            //var id = repository.InsertReturnIdentity(insertObj);
            //repository.AsInsertable(insertObj).ExecuteCommand();

            // 删除
            //repository.Delete(insertObj);
            //repository.Delete(it => it.Id == 1);

            // 更新
            //repository.Update(insertObj);
            //repository.Update(it => new Order() { Name = "a", }, it => it.Id == 1);

            // 异步方法用法
            //repository.Insert(insertObj); // 同步
            //repository.InsertAsync(insertObj); // 异步

            // 切换仓储
            var orderRespository = db.GetSimpleClient<Order>();
            //orderRespository.Insert(Order);

            #endregion

            #region SqlSugarClient 操作示例

            #region 基础查询

            // 查询所有
            var getAll = db.Queryable<Student>().ToList();
            // 查询前10
            var top10 = db.Queryable<Student>().Take(10).ToList();
            // 查询单条
            var getFirst = db.Queryable<Student>().First(t => t.Id == 1);
            // with nolock
            var getAllNoLock = db.Queryable<Student>().With(SqlWith.NoLock).ToList();
            // 根据主键查询
            var getByPrimaryKey = db.Queryable<Student>().InSingle(2);
            // 查询总和
            var sum = db.Queryable<Student>().Sum(t => t.Id);
            // 是否存在
            var isAny = db.Queryable<Student>().Where(t => t.Id == -1).Any();
            // 模糊查询
            var list2 = db.Queryable<Order>().Where(it => it.Name.Contains("jack")).ToList();

            #endregion

            #region 联表查询

            var list = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(
                JoinType.Left, st.SchoolId == sc.Id))
                .Select((st, sc) => new { Id = st.Id, Name = st.Name, SchoolName = sc.Name }).ToList();

            // 生成的Sql如下：
            //SELECT[st].[ID] AS[id] ,
            //      [st].[Name] AS[name] ,
            //      [sc].[Name] AS[schoolName]  FROM[STudent] st
            //      Left JOIN School sc ON([st].[SchoolId] =[sc].[Id])

            #endregion

            #region 分页查询

            int pageIndex = 1;
            int pageSize = 20;
            int totalCount = 0;
            var page = db.Queryable<Student>().ToPageList(pageIndex, pageSize, ref totalCount);

            // 更多查询用法：http://www.donet5.com/Home/Doc?typeId=1185

            #endregion

            #region 插入

            // 可以是类或者List<类>
            //db.Insertable(insertObj).ExecuteCommand();

            // 插入返回自增列
            //db.Insertable(insertObj).ExecuteReturnIdentity();

            // 可以是Dictionary或者List<Dictionary>
            var dc = new Dictionary<string, object>();
            dc.Add("name", "1");
            dc.Add("CreateTime", null);
            db.Insertable(dc).AS("student").ExecuteCommand();

            // DataTable插入
            DataTable dt = new DataTable();
            dt.Columns.Add("fRoleID");
            dt.Columns.Add("fNodeID");
            Dictionary<string, object> dct = db.Utilities.DataTableToDictionary(dt);//转成字典就可以按上面的字典更新了
            db.Insertable(dc).AS("student").ExecuteReturnIdentity();

            // 更多插入用法：http://www.donet5.com/Home/Doc?typeId=1193

            #endregion

            #region 更新

            // 实体配置参考插入，只需要配置主键即可

            // 根据主键更新单条 参数 Class
            //var result = db.Updateable(updateObj).ExecuteCommand();
            // 不更新 Name 和TestId
            //var result1 = db.Updateable(updateObj).IgnoreColumns(it => new { it.CreateTime, it.TestId }).ExecuteCommand()
            // 只更新 Name 和 CreateTime
            //var result2 = db.Updateable(updateObj).UpdateColumns(it => new { it.Name, it.CreateTime }).ExecuteCommand();
            // 根据表达式更新
            var result71 = db.Updateable<Order>()
                          .SetColumns(it => it.Name == "a")
                          //.SetColumnsIF(p != null, it => it.CreateTime == p.Value)// 当p不等于null更新createtime列
                        .Where(it => it.Id == 11).ExecuteCommand();

            // 更多更新用法：http://www.donet5.com/Home/Doc?typeId=1191

            #endregion

            #region 删除

            // 实体配置参考插入，只需要配置主键即可

            // 根据实体删除
            db.Deleteable<Student>().Where(new Student() { Id = 1 }).ExecuteCommand();
            // 根据主键删除
            db.Deleteable<Student>().In(1).ExecuteCommand();
            // 根据表达式删除
            db.Deleteable<Student>().Where(t => t.Id == 1).ExecuteCommand();

            // 更多删除用法：http://www.donet5.com/Home/Doc?typeId=1195

            #endregion

            #region Sql查询

            // sql分页
            int total = 0;
            var pageList = db.SqlQueryable<Student>("select * from student").ToPageList(1, 2, ref total);
            // 原生Sql写法
            var dtSql = db.Ado.GetDataTable("select * from table where id = @id and name = @name", new List<SugarParameter>() {
                new SugarParameter("@id", 1),
                new SugarParameter("@name", 2)
            });
            // 参数2
            var dtParameter = db.Ado.GetDataTable("select * from table where id = @id and name = @name", new { id = 1, name = 2 });

            // 存储过程
            var nameP = new SugarParameter("@name", "张三");
            var ageP = new SugarParameter("@age", null, true); // 设置为output
            var dtProcedure = db.Ado.UseStoredProcedure().GetDataTable("sp_school", nameP, ageP);

            #endregion

            #endregion
        }

        #endregion

        #region Dapper

        public async Task DapperDemoAsync()
        {
            #region Sql操作

            var data = _dapperRepository.Query("select * from person");
            var dataA = await _dapperRepository.QueryAsync("select * from person");

            var dataT = _dapperRepository.Query<Person>("select * from person");

            var guid = Guid.NewGuid();
            var dog = _dapperRepository.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });

            var count = _dapperRepository.Execute(@"insert MyTable(colA, colB) values (@a, @b)", new[] { new { a = 1, b = 1 }, new { a = 2, b = 2 }, new { a = 3, b = 3 } });

            var user = _dapperRepository.Query<User>("spGetUser", new { Id = 1 }, commandType: CommandType.StoredProcedure).SingleOrDefault();

            // 用法和官方一致，在此不再举更多例子

            #endregion

            #region <TEntity>操作

            // Furion框架提供了IDapperRepository和IDapperRepository<TEntity>两个操作仓储，后者继承前者。使用如下：
            var person = _dapperTRepository.Get(1);
            var persons = _dapperTRepository.GetAll();

            var effectInsert = _dapperTRepository.Insert(person);
            var effectUpdate = _dapperTRepository.Update(person);
            var effectDelete = _dapperTRepository.Delete(person);

            var effectsInsert = _dapperTRepository.Insert(persons); // 插入多个
            var effectsUpdate = _dapperTRepository.Update(persons); // 更新多个
            var effectsDelete = _dapperTRepository.Delete(persons); // 删除多个

            var effect = await _dapperTRepository.InsertAsync(person); // 异步

            #endregion

            #region 高级使用

            // IDapperRepository和IDapperRepository<TEntity>仓储提供了Context和DynamicContext属性，该属性返回IDbConnection对象

            #region 查询一对一

            var sql = @"select * from #Posts p left join #Users u on u.id = p.OwnerId Order by p.Id";

            var dataSql = _dapperRepository.Context.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post; });
            var post = data.First();

            #endregion

            #region 查询多个结果

            var sqlMore =
            @"
            select * from Customers where CustomerId = @id
            select * from Orders where CustomerId = @id
            select * from Returns where CustomerId = @id";

            int selectedId = 0;
            using (var multi = _dapperRepository.Context.QueryMultiple(sqlMore, new { id = selectedId }))
            {
                var customer = multi.Read<Customer>().Single();
                var orders = multi.Read<Order>().ToList();
                var returns = multi.Read<Return>().ToList();
                // ...
            }

            #endregion

            #region 更多操作

            var shapes = new List<IShape>();
            using (var reader = _dapperRepository.Context.ExecuteReader("select * from Shapes"))
            {
                var circleParser = reader.GetRowParser<IShape>(typeof(Circle));
                var squareParser = reader.GetRowParser<IShape>(typeof(Square));
                var triangleParser = reader.GetRowParser<IShape>(typeof(Triangle));

                var typeColumnIndex = reader.GetOrdinal("Type");

                while (reader.Read())
                {
                    IShape shape;
                    var type = (ShapeType)reader.GetInt32(typeColumnIndex);
                    switch (type)
                    {
                        case ShapeType.Circle:
                            shape = circleParser(reader);
                            break;
                        case ShapeType.Square:
                            shape = squareParser(reader);
                            break;
                        case ShapeType.Triangle:
                            shape = triangleParser(reader);
                            break;
                        default:
                            throw new NotImplementedException();
                    }

                    shapes.Add(shape);
                }
            }

            #endregion

            #endregion
        }

        #endregion

        #region MongoDB

        public async Task MongoDBAsync()
        {
            // BsonDocument类示例
            var database = _mongoDBRepository.Context.GetDatabase("foo");
            var collection = database.GetCollection<BsonDocument>("bar");

            await collection.InsertOneAsync(new BsonDocument("Name", "Jack"));

            var list = await collection.Find(new BsonDocument("Name", "Jack")).ToListAsync();

            foreach (var document in list)
            {
                Console.WriteLine(document["Name"]);
            }

            // Person类示例
            var databasePerson = _mongoDBRepository.Context.GetDatabase("foo");
            var collectionPerson = database.GetCollection<Person>("bar");

            await collectionPerson.InsertOneAsync(new Person { Name = "Jack" });

            var listPerson = await collectionPerson.Find(x => x.Name == "Jack")
                                       .ToListAsync();

            foreach (var person in listPerson)
            {
                Console.WriteLine(person.Name);
            }

            // 获取MongoClient对象
            // IMongoDBRepository只封装了MongoDB基础功能，如需获取更多操作可通过.Context属性获取MongoClient对象
            var client = _mongoDBRepository.Context;
        }

        #endregion

        #region 自定义租户

        public void CustomTenant()
        {
            // 查询租户信息
            var tenantDbContext = Db.GetDbContext<Core.MultiTenantDbContextLocator>();
            var myTenant = tenantDbContext.Set<MyTenant>();

            #region 刷新租户缓存

            // Furion框架会在租户上下文第一次查询时候将租户表缓存起来，避免频发查询数据库，如果更新了租户表，则需要手动刷新租户信息

            // 在更新租户信息后调用
            //_repository.Context.RefreshTenantCache();

            #endregion
        }

        #endregion
    }
}
